﻿using QWManage.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace QWManage.DAL
{
    public class GroupDAL : SqlDAL
    {
        public GroupDAL()
        {
            TableName = "QW_Groups";
        }
        public List<GroupInfo> GetGroupInfos(List<string> ids)
        {
            List<GroupInfo> list = new List<GroupInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                conn.Open();
                string sql = $"select * from {TableName} where ChatID=@ChatID";
                SqlCommand cmd = new SqlCommand(sql, conn);
                foreach (string id in ids)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(new SqlParameter("@ChatID", id));
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            GroupInfo groupInfo = GetGroupInfo(reader);
                            list.Add(groupInfo);
                        }
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }
        public List<GroupInfo> GetGroupInfos()
        {
            List<GroupInfo> list = new List<GroupInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = "select * from " + TableName;
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        GroupInfo obj = GetGroupInfo(reader);
                        list.Add(obj);
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }
        public bool Append(string chatID, string name)
        {
            GroupInfo oldObj = GetGroupChatInfo(chatID);
            if (oldObj != null)
            {
                oldObj.Name = name;
                return Update(oldObj);
            }
            else
                return Insert(new GroupInfo() { ChatID = chatID, Name = name });
        }
        public bool Append(GroupInfo obj)
        {
            GroupInfo oldObj = GetGroupChatInfo(obj.ChatID);
            if (oldObj != null)
            {
                obj.Interval = oldObj.Interval;//保留interval
                return Update(obj);
            }
            else
                return Insert(obj);
        }
        public bool UpdateGroupStatus(string chatid, bool disabled)
        {
            string sql = $"update {TableName} set Disabled=@Disabled where ChatID=@ChatID";
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@Disabled", disabled));
                cmd.Parameters.Add(new SqlParameter("@ChatID", chatid));
                return cmd.ExecuteNonQuery() == 1;
            }
            finally
            {
                conn.Close();
            }
        }
        public bool UpdateGroupInterval(string chatid, int? interval)
        {
            string sql = $"update {TableName} set Interval=@Interval where ChatID=@ChatID";
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (interval.HasValue)
                    cmd.Parameters.Add(new SqlParameter("@Interval", interval));
                else
                    cmd.Parameters.Add(new SqlParameter("@Interval", DBNull.Value));

                cmd.Parameters.Add(new SqlParameter("@ChatID", chatid));
                return cmd.ExecuteNonQuery() == 1;
            }
            finally
            {
                conn.Close();
            }
        }
        public bool UpdateGroupName(string chatid, string name)
        {
            string sql = $"update {TableName} set Name=@Name where ChatID=@ChatID";
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@Name", name));
                cmd.Parameters.Add(new SqlParameter("@ChatID", chatid));
                return cmd.ExecuteNonQuery() == 1;
            }
            finally
            {
                conn.Close();
            }
        }
        public bool Update(GroupInfo obj)
        {
            string sql = $"update {TableName} set Name=@Name, Interval=@Interval where ChatID=@ChatID";
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@Name", obj.Name));
                cmd.Parameters.Add(new SqlParameter("@ChatID", obj.ChatID));
                if (obj.Interval.HasValue)
                    cmd.Parameters.Add(new SqlParameter("@Interval", obj.Interval));
                else
                    cmd.Parameters.Add(new SqlParameter("@Interval", DBNull.Value));
                return cmd.ExecuteNonQuery() == 1;
            }
            finally
            {
                conn.Close();
            }
        }
        public bool Insert(GroupInfo obj)
        {
            string sql = $"insert into {TableName} " +
                $"(ChatID,Name,Interval) values(@ChatID,@Name,@Interval) ";
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@ChatID", obj.ChatID));
                cmd.Parameters.Add(GetStringSqlParameter("@Name", obj.Name));

                if (obj.Interval.HasValue)
                    cmd.Parameters.Add(new SqlParameter("@Interval", obj.Interval));
                else
                    cmd.Parameters.Add(new SqlParameter("@Interval", DBNull.Value));

                conn.Open();
                return cmd.ExecuteNonQuery() == 1;
            }
            finally
            {
                conn.Close();
            }
        }
        public GroupInfo GetGroupChatInfo(string chatid)
        {
            GroupInfo obj = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where ChatID=@ChatID";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@ChatID", chatid));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        obj = GetGroupInfo(reader);
                    }
                }
                return obj;
            }
            finally
            {
                conn.Close();
            }
        }
        private GroupInfo GetGroupInfo(SqlDataReader reader)
        {
            GroupInfo obj = new GroupInfo();
            obj.ChatID = ReadString(reader, "ChatID");
            obj.Name = ReadString(reader, "Name");
            obj.Interval = ReadIntNull(reader, "Interval");
            if (obj.Interval == 0) obj.Interval = null;

            if (reader["Disabled"] == DBNull.Value)
                obj.Disabled = false;
            else obj.Disabled = ReadBool(reader, "Disabled");

            return obj;
        }

    }
}
